<?php

class Core_Model_DbTable_Contests extends YouNet_Db_Table implements Core_Model_IContest
{
    const TABLE_NAME 			= 'contests';


    protected $_name            = self::TABLE_NAME;
    protected $_rowClass        = 'Core_Model_Contest';
	protected $_dependentTables = array(
									'Core_Model_DbTable_Prizes',
									'Core_Model_DbTable_Phases',
									'Core_Model_DbTable_Pages',
								);
	private static $_instance;
	/**
	 * @author hungnq
	 * @return Core_Model_DbTable_Contests
	 */
	public static function getInstance()
	{
		if(is_null(self::$_instance))
		{
			self::$_instance = new self();
		}
		return self::$_instance;
	}

	/**
	 *
	 * @param type $idContest
	 * @return Core_Model_Contest
	 */
	public static function getContestRow($idContest = 0)
	{
		$self	= new Core_Model_DbTable_Contests();
		$row	= null;
		if( $idContest > 0)
		{
			$row = $self->findOne($idContest);
		}
		else
		{
			$row = $self->createRow();
		}
		return $row;
	}

	/**
	 * @author hungnq@younetco.com
	 * @return type
	 */
	public function getContestsLis()
	{
		$query = $this->select()
			->setIntegrityCheck(false)
			->from(array('c' => self::TABLE_NAME),'c.*')
			->join(
				array('u' => Core_Model_DbTable_Users::TABLE_NAME),
				'u.id = c.id_user',
				array(
					'id_user'    => 'u.id',
					'name_user'  => 'u.name'
				)
			)
			->joinLeft(
				array('p' => Core_Model_DbTable_Pages::TABLE_NAME),
				'c.id = p.id_contest',
				array(
					'name_pages'	=> new Zend_Db_Expr("GROUP_CONCAT(DISTINCT p.name_page SEPARATOR '^')"),
					'id_pages'		=> new Zend_Db_Expr("GROUP_CONCAT(DISTINCT p.id_page)")
				)
			);
		return $this->fetchAll($query);
	}
	/**
	 * Get contest list which are managed by admin
	 * @param int $id_user
	 * @param array $arr_id_pages
	 * @param array $arrSort
	 * @return Zend_Db_Select
	 */
	public function getSelectionContestsListByAdmin($id_user,array $arr_id_pages, array $arrSort = null)
	{
		$select = $this->select()
			->setIntegrityCheck(false)
			->from(array('c' => self::TABLE_NAME),'c.*')
			->join(
				array('u' => Core_Model_DbTable_Users::TABLE_NAME),
				'u.id = c.id_user',
				array(
					'name_user'  => 'u.name'
				)
			)
			->joinLeft(
				array('p1' => Core_Model_DbTable_Pages::TABLE_NAME),
				'c.id = p1.id_contest',
				array(
				)
			)
			->joinLeft(
				array('p' => Core_Model_DbTable_Pages::TABLE_NAME),
				'c.id = p.id_contest',
				array(
					'id_page'		=> 'id_page',
					'name_pages'	=> new Zend_Db_Expr("GROUP_CONCAT(DISTINCT p.name_page SEPARATOR '^')"),
					'id_pages'		=> new Zend_Db_Expr("GROUP_CONCAT(DISTINCT p.id_page)"),					
				)
			)
			->joinLeft(
				array('f' => Core_Model_DbTable_Phases::TABLE_NAME),
				'f.id_contest = c.id',
				array(
					'date_start' => new Zend_Db_Expr('MIN(f.date_start)'),
					'date_end' => new Zend_Db_Expr('MAX(f.date_end)')
				)
			);

		if( !empty($arrSort['q']) )
		{
			$select->where('c.title LIKE ?', '%'.$arrSort['q'].'%');
		}
        if( !empty($arr_id_pages) )
        {
            $str_ids     = '\''.implode('\',\'',$arr_id_pages).'\'';
            $select->where($this->_db->quoteInto('id_user = ?', $id_user)." OR p1.id_page IN ($str_ids)");
        } else {
            $select->where('c.id_user = ?',$id_user);
        }

        $select->group('c.id');

		$isSort = false;
		if( !empty($arrSort) )
		{
			foreach($arrSort as $key => $value)
			{
				if( in_array($key, array('title','registrants','submissions','status','date_start','date_end')))
				{
					if( in_array($value, array('DESC','ASC')) )
					{
						$isSort = true;
						$select->order("$key $value");
						$select->order('id DESC');
					}
				}
			}
		}
		if( !$isSort )
		{
			$select->order('date_start DESC');
			$select->order('id DESC');
		}
        return $select;
	}

	public function getSelectionContestsListIdPage($id_page, array $arrSort = null)
	{
		if( !$id_page )
		{
			throw new Exception('Bad request',404);;
		}
		$select = $this->select()
			->setIntegrityCheck(false)
			->from(array('c' => self::TABLE_NAME),'c.*')
			->join(
				array('u' => Core_Model_DbTable_Users::TABLE_NAME),
				'u.id = c.id_user',
				array(
					'id_user'    => 'u.id',
					'name_user'  => 'u.name'
				)
			)
			->joinLeft(
				array('p' => Core_Model_DbTable_Pages::TABLE_NAME),
				'c.id = p.id_contest',
				array(
					'name_pages'	=> new Zend_Db_Expr("GROUP_CONCAT(DISTINCT p.name_page SEPARATOR '^')"),
					'id_pages'		=> new Zend_Db_Expr("GROUP_CONCAT(DISTINCT p.id_page)")
				)
			)
			->join(
				array('p1' => Core_Model_DbTable_Pages::TABLE_NAME),
				'c.id = p1.id_contest',
				array(
				)
			)
			->joinLeft(
				array('f' => Core_Model_DbTable_Phases::TABLE_NAME),
				'f.id_contest = c.id',
				array(
					'date_start' => new Zend_Db_Expr('MIN(f.date_start)'),
					'date_end' => new Zend_Db_Expr('MAX(f.date_end)')
				)
			);

		$select->where("p1.id_page = ?",$id_page);
		if( !empty($arrSort['q']) )
		{
			$select->where('c.title LIKE ?', '%'.$arrSort['q'].'%');
		}
        $select->group('c.id');

		$isSort = false;
		if( !empty($arrSort) )
		{
			foreach($arrSort as $key => $value)
			{
				if( in_array($key, array('title','registrants','submissions','status','date_start','date_end')))
				{
					if( in_array($value, array('DESC','ASC')) )
					{
						$isSort = true;
						$select->order("$key $value");
					}
				}
			}
		}
		if( !$isSort )
		{
			$select->order("date_start DESC");
		}

        return $select;
	}

	/**
	 * Check if a fanpage has yet published + not yet end a contest
	 * @param $id_page
	 * @return Boolean
	 */
	public function hasContestIsPublished($id_page)
	{
		/*
		$sql = 'SELECT count(*) > 0
			FROM contests c
			JOIN contest_pages cp
			ON cp.id_contest = c.id and cp.id_page = :id_page
			WHERE c.status = :status
			AND UNIX_TIMESTAMP() <
				(
					SELECT MAX(p.date_start)
					FROM contest_phases p
					WHERE p.id_contest = c.id
				)';
		*/
		$sql = 'SELECT count(*) > 0
			FROM contests c
			JOIN contest_pages cp
			ON cp.id_contest = c.id and cp.id_page = :id_page
			WHERE c.status = :status';
		return $this->getAdapter()->fetchOne(
			$sql,
			array(
				'id_page'	=> $id_page,
				'status'	=> self::STATUS_PUBLISHED
			)
		);
	}
	
	public function getAllRegistrantsByContestId($id_contest, &$arrColumns)
	{
		$contestFieldsTable = new Core_Model_DbTable_EntryFields();
		$contestFields		= $contestFieldsTable->getListFieldsBelongToContest($id_contest);
		$arrHeader			= array();
		foreach($contestFields as $field)
		{
			//Don't export first/last/middle name
			if( $field['type'] != 1 && $field['type'] != 2 && $field['type'] != 3)
			{
				$arrHeader[$field['id']]	= $field['label'];
			}
		}
		$select = $this->getDefaultAdapter()->select();
		$select->from(
			array(
				'ce' => Core_Model_DbTable_Entries::TABLE_NAME
			),
			array(
				'time_uploaded'	=> 'time_uploaded',
				'entries'		=> new Zend_Db_Expr('count(ce.id)'),
                'id_user'       => 'id_user'
			)
		);
		
		$i = 0;
		$country_column = null;
		foreach($arrHeader as $id => $aliasColumn)
		{
			$i++;
			$aliasTbl		= 'ci'.$i;
			if($aliasColumn == 'FIELD_COUNTRY')
			{
				$country_column = $i;
			}
			$arrColumns[]	= $aliasColumn;
			$select->joinLeft(
				array(
					$aliasTbl => Core_Model_DbTable_CandidateInfo::TABLE_NAME
				),
				"{$aliasTbl}.id_entry_field = {$id} AND {$aliasTbl}.id_user = ce.id_user",
				array(					
					$aliasColumn => 'value'
				)
			);
		}
		
		if(!empty($country_column))
		{
			$select->joinLeft(array('cn'=>'countries'),'cn.id = ci'.$country_column .'.value','cn.country as country');
		}
				
		$select->where('ce.id_contest = ?', $id_contest);
		$select->group('ce.id_user');
		return $select->query(Zend_Db::FETCH_ASSOC);
	}
	
	public function getShareContest($id_contest, $id_page)
	{
		$query = $this->select()->from(array('c' => self::TABLE_NAME),'c.*')
				->setIntegrityCheck(false)
			->joinLeft(
				array('cp' => Core_Model_DbTable_Pages::TABLE_NAME),'cp.id_contest = c.id',
				array('name_page'  => 'cp.name_page')
			)->where('c.id=?',$id_contest)->where('cp.id_page=?', $id_page);
		return $this->fetchAll($query)->current();
	}
	
	public static function removePageCache($id_contest)
	{
		return YouNet_Util::getObjectMemCache()->remove(Core_Cache_IKeys::MEMCACHE_CONTEST_ID_PAGES.$id_contest);
	}
    public static function removeContestCache($id_contest){
        $key = Core_Cache_IKeys::MEMCACHE_MODEL_CONTEST.$id_contest;
        return YouNet_Util::getObjectMemCache()->remove($key);
    }
    public static function removeIndexIdEntriesCache($id_contest){
        $key = Core_Cache_IKeys::MEMCACHE_INDEX_ID_ENTRY_APPROVE.$id_contest;
        return YouNet_Util::getObjectMemCache()->remove($key);
    }
    public static function getListSupportedLanguage()
	{
		return array(
			'en_US' => 'English',
			'vi_VN' => 'Tiếng Việt'
		);
	}
	
	public static function getListView()
	{
		return array(
			Core_Model_Contest::ABOUT_VIEW	=> 'About the contest',
			Core_Model_Contest::ENTRIES_VIEW	=> 'View entries'
		);
	}
    
    public function updateSubmissionInfo($id_contest, $options = null){
        $result = null;
        if(is_array($options)){
            $data = array();
            if(!empty($options['submissions'])){
                $data['submissions'] = $options['submissions'];
            }
            if(!empty($options['registrants'])){
                $data['registrants'] = $options['registrants'];
            }
            $result = $this->update(
                $data,
                array('id = ?' => $id_contest)
            );
            if(!empty($result)){
                $this->removeContestCache($id_contest);
            }
        }
        return $result;
    }
	
	public function getStatisticData($id_contest, $fromDate, $toDate)
	{
		$id_pages   = Core_Model_DbTable_Pages::getInstance()->getArrayPagesID($id_contest);
		$visitorQuery = "(SELECT COUNT(*) FROM " . YouNet_Model_DbTable_Visitors::TABLE_NAME
					. " WHERE time_visited >= " . $fromDate ." AND time_visited <= " . $toDate
					. " AND id_campaign = " . $id_contest . ")";
		$newFanQuery = "(SELECT COUNT(*) FROM " . YouNet_Model_DbTable_Likes::TABLE_NAME
					. " WHERE time_liked >= " . $fromDate ." AND time_liked <= " . $toDate
					. " AND id_campaign = " . $id_contest . ")";
		$votes = "(SELECT COUNT(*) FROM " . Core_Model_DbTable_Votes::TABLE_NAME . " AS v"
					. " INNER JOIN " . Core_Model_DbTable_Entries::TABLE_NAME . " AS e" . " ON v.id_entry = e.id "
					. " WHERE v.time_voted >= " . $fromDate ." AND v.time_voted <= " . $toDate
					. " AND e.id_contest = " . $id_contest . ")";
		$voter = "(SELECT COUNT(DISTINCT(v.id_user)) FROM " . Core_Model_DbTable_Votes::TABLE_NAME . " AS v"
					. " INNER JOIN " . Core_Model_DbTable_Entries::TABLE_NAME . " AS e" . " ON v.id_entry = e.id "
					. " WHERE v.time_voted >= " . $fromDate ." AND v.time_voted <= " . $toDate
					. " AND e.id_contest = " . $id_contest . ")";
		$select = $this->select()
			->from(Core_Model_DbTable_Contests::TABLE_NAME, array(
				'visitor' => new Zend_Db_Expr($visitorQuery),
				'new_fan' => new Zend_Db_Expr($newFanQuery),
				'votes' => new Zend_Db_Expr($votes),
				'voter' => new Zend_Db_Expr($voter),
				'registrants',
				'entries'	=> 'submissions'
			))
			->where('id = ?', $id_contest);	
		$a = self::getDefaultAdapter()->query($select);
		return $a->fetch();
	}
	public static function getDataColorCode()
	{
		return array(
			YouNet_Model_DbTable_Visitors::STATISTIC_KEY => '#1E98CC',
			YouNet_Model_DbTable_Likes::STATISTIC_KEY => '#50B432',
			Core_Model_DbTable_Votes::STATISTIC_KEY => '#EA911F'
		);
	}
}
